#use encoding 'utf-8';
binmode STDOUT, ":utf8";
use DBI;
use CGI;
#use strict;

my $dbh= DBI->connect ('dbi:ODBC:nrega1','','') || die "could not connect\n";

#
#
#
=head
my $query="SELECT UP40MustRoll0910.Panchayat_Code, PANCHAYATS.Panchayat_Name, UP40MustRoll0910.Work_Code, UPWork_detail.work_name, UP40MustRoll0910.Tot_Attend, UP40MustRoll0910.Wage_Per_day, UP40MustRoll0910.amt_paid_measure, UP40MustRoll0910.MSR_No
FROM ((UP40Musterrollmaster0910 INNER JOIN (UP40MustRoll0910 INNER JOIN UPWork_detail ON (UP40MustRoll0910.Block_code = UPWork_detail.block_code) AND (UP40MustRoll0910.Panchayat_Code = UPWork_detail.Panchayat_code) AND (UP40MustRoll0910.Work_Code = UPWork_detail.work_code)) ON (UP40Musterrollmaster0910.Block_code = UP40MustRoll0910.Block_code) AND (UP40Musterrollmaster0910.MSR_No = UP40MustRoll0910.MSR_No) AND (UP40Musterrollmaster0910.Work_Code = UP40MustRoll0910.Work_Code)) INNER JOIN PANCHAYATS ON UP40MustRoll0910.Panchayat_Code = PANCHAYATS.Panchayat_Code) INNER JOIN Exe_Agency ON UPWork_detail.Exe_Agency_code = Exe_Agency.Exe_Agency_code
ORDER BY UP40MustRoll0910.Panchayat_Code, UP40MustRoll0910.Work_Code, UP40MustRoll0910.Tot_Attend DESC , UP40MustRoll0910.MSR_No DESC";
****/
=cut
my $query1="select distinct Panchayats.district_code,Panchayats.block_code,Panchayats.panchayat_code,Districts.district_name,Blocks.block_name,Panchayats.panchayat_name from 
(Panchayats inner join Districts 
on districts.district_code=panchayats.district_code)
inner join blocks  
on blocks.block_code=panchayats.block_code";
my $sth=$dbh->prepare($query1);
$sth->execute();
my $district=0;
my $block=0;
my $panchayat=0;
my $count=0;
my %panchayats; #hash of hashes{ district_code=>{block_code=>(panchayat1,panchayat2,....),...},....}
%districts;
%blocks;
%panchayatnames;
while (@ary=$sth->fetchrow_array ()) {
 $district=($ary[0]!=$district) ? $ary[0]:$district;
 $block=($ary[1]!=$block) ? $ary[1]:$block;
 $panchayat=($ary[2]!=$panchayat) ? $ary[2]:$panchayat;
 $panchayatnames{$panchayat}=$ary[5];
 $districts{$district}=$ary[3];
 $blocks{$block}=$ary[4];
 #print "$count : $district $block $panchayat \n";
 $count++;
  #if (exists $panchayats[$block]){
  push @{$panchayats{$district}{$block}},$panchayat;
  #} else {
  #$panchayats{$block}=["$panchayat"];
  #}
 
}
=head
print %panchayats;
use Data::Dumper;
print Dumper(%panchayats);
=cut
$count=1;
$maindir="c:/nregaout";
foreach $district(keys(%panchayats)) {
my $distdir = $maindir."/".$districts{$district};
mkdir $distdir;
  foreach $block(keys %{$panchayats{$district}}) {
   $blockdir= $distdir."/".$blocks{$block};
   if (! -d $blockdir) { mkdir $blockdir;}
	foreach $panchayat(@{$panchayats{$district}{$block}}) {
		$panchayatdir= $blockdir."/".$panchayatnames{$panchayat};
		if (! -d $panchayat) { mkdir $panchayatdir;}
		
	
		print "$count : $district $block $panchayat \n";
		$count++;
		my $query1="select distinct reg_no from UP40Registration where panchayat_code=?";
		$sth=$dbh->prepare($query1);
		$sth->execute($panchayat);
		while (@arry=$sth->fetchrow_array ()) {
			#print $arry[0];
			push @workers,$arry[0];
			my $x=$arry[0];
			#print $x;
			#exit;
			my $query2="select head_of_household,father_or_husband_name from UP40Registration where reg_no=?";
			my $sth1=$dbh->prepare($query2);
			$result= $sth1->execute($x);
		    if ($result) {
			@arry1=$sth1->fetchrow_array ();
			$workernames{$arry[0]}="$arry1[0] $arry1[1]";
			} else {
			print "error\n";
			}
			
		}
		#print join(',',@workcodes);
		#exit;
		#break;
		#print %workernames;
		#exit;
		
		my $query="SELECT    UPWork_detail.work_name,sum(UP40MustRoll0910.Tot_Attend),  sum(UP40MustRoll0910.amt_paid_measure), 
		UP40MustRoll0910.MSR_No
FROM UP40MustRoll0910 INNER JOIN UPWork_detail on 
 UP40MustRoll0910.Work_Code = UPWork_detail.work_code 
 
WHERE  UP40MustRoll0910.reg_no=?
GROUP BY UPWork_detail.work_name,UP40MustRoll0910.MSR_No";

#ORDER BY PANCHAYATS.Panchayat_Name, UPWork_detail.work_name, UP40MustRoll0910.Tot_Attend DESC , UP40MustRoll0910.MSR_No DESC";
#GROUP By PANCHAYATS.Panchayat_Name,UP40MustRoll0910.Panchayat_code,UP40MustRoll0910.MSR_No
		$sth=$dbh->prepare($query);
		my $q= new CGI;
		open $fh,"> $panchayatdir/workers.html";
		binmode $fh ,":utf8";
		
		#print $fh "Content-type:text/html;charset=utf-8;\n\n";
		#print $fh "Content-type: text/html;charset=utf-8";

		#('content="text/html; charset=utf-8"');
		print $fh qq(<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Work details</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="robots" content="index, follow" />

<link rel="stylesheet" href="../../../styles.css" type="text/css" />
 </head>);
		#print $fh qq(<head> <link rel=stylesheet href="../../../style.css"></head>);
		print $fh qq(<body style="margin: 0px; padding: 0px;");
		print $fh qq(<h3 align=center> $panchayatnames{$panchayat}: Workers  in financial year 2009-10 </h3>);

		print $fh qq(<table  style="height: 100%;width:70%;margin-top:40;" align=center cellpadding="10" cellspacing="0" border="1" >\n); # manipulte to look like you want it
 
		print $fh "<thead>";
		print $fh "<th>Sl.No.</th><th>Name of Worker</th><th>Work(Muster roll_no(no_of_days)</th><th>Total Attend</th><th>Total Paid</th>";
		print $fh "</thead>";
		$count=1;
		foreach my $worker(@workers) {
			
			#print "$count: $panchayat $workcode \n";
			$sth->execute($worker);
			$tot_attend=0;
			$tot_paid=0;
			@mustrolls=();
			print $fh "<tr> \n";
			
			while (@arry=$sth->fetchrow_array ()) {
				
				#print @arry;
				push @mustrolls,$arry[0]."(".$arry[3]."(".$arry[1].")".")";
				$workname= $arry[0];
				$tot_attend += $arry[1];
				$tot_paid   += $arry[2];
			}
			#print ".";
			if (@mustrolls) {
			print $fh "  <td>\n";
			print $fh "		$count\n";
			print $fh "	 </td> \n";
			
			print $fh "  <td>\n";
			print $fh "		$workernames{$worker}<br><font size=-2>[$worker]</font></br>\n";
			print $fh "	 </td> \n";
			
			print $fh "  <td>\n";
			print $fh "		".join(',',@mustrolls)."\n";
			print $fh "	 </td> \n";
			print $fh "  <td>\n";
			print $fh "		$tot_attend\n";
			print $fh "	 </td> \n";
			print $fh "  <td>\n";
			print $fh "		$tot_paid\n";
			print $fh "	 </td> \n";
			print $fh "</tr> \n";
			$count++;
			}
			#@mustrolls=();
		}
		print $fh "</table>";
		print $fh "</body></html>";
		close $fh;
		exit;

	}
  }
}
=head
my $query1="select distinct(UPWork_detail.work_code) from UPWork_detail";
$sth=$dbh->prepare($query1);
$sth->execute();
while ($sth->fetchrow_array ()) {
push @workcodes,$_;
}
my $query1="select distinct(UPWork_detail.Panchayat_code) from UPWork_detail";
$sth=$dbh->prepare($query1);
$sth->execute();
while (@ary=$sth->fetchrow_array ()) {
foreach (@ary) {
push @panchayatcodes,$_;}
}
#print @panchayatcodes;
#$sth=$dbh->prepare($query);
#$sth->execute();
$count=1;


my $query="SELECT  PANCHAYATS.Panchayat_Name, UPWork_detail.work_name, UP40MustRoll0910.Tot_Attend, UP40MustRoll0910.Wage_Per_day, UP40MustRoll0910.amt_paid_measure, UP40MustRoll0910.MSR_No
FROM ((UP40Musterrollmaster0910 INNER JOIN (UP40MustRoll0910 INNER JOIN UPWork_detail ON (UP40MustRoll0910.Block_code = UPWork_detail.block_code) AND (UP40MustRoll0910.Panchayat_Code = UPWork_detail.Panchayat_code) AND (UP40MustRoll0910.Work_Code = UPWork_detail.work_code)) ON (UP40Musterrollmaster0910.Block_code = UP40MustRoll0910.Block_code) AND (UP40Musterrollmaster0910.MSR_No = UP40MustRoll0910.MSR_No) AND (UP40Musterrollmaster0910.Work_Code = UP40MustRoll0910.Work_Code)) INNER JOIN PANCHAYATS ON UP40MustRoll0910.Panchayat_Code = PANCHAYATS.Panchayat_Code) INNER JOIN Exe_Agency ON UPWork_detail.Exe_Agency_code = Exe_Agency.Exe_Agency_code
WHERE  UPWork_detail.Panchayat_code=?
ORDER BY PANCHAYATS.Panchayat_Name, UPWork_detail.work_name, UP40MustRoll0910.Tot_Attend DESC , UP40MustRoll0910.MSR_No DESC";
foreach $panchayat(@panchayatcodes) {
	print "$count: $panchayat \n";
	$count++;
	$dir="c:/nregaout/$
	$file ="c:/nregaout/nrega.html";
	open $fh, ">> $file" || die "count not open $file\n";



	print $fh $q->header;
	print $fh $q->start_html (-title=>'Here you go omniscientbeing',
-BGCOLOR=>'white',-TEXT=>'black');

print $fh "<table>\n"; # manipulte to look like you want it
 
 # read results of query, then clean up 

 while (@ary = $sth->fetchrow_array ()) 
 { 
  print $fh "  <tr>\n"; 
  print $fh "    <td>$ary[0]</td>\n";
  foreach(@ary) 
  { 
   print $fh "    <td>$_</td>\n"; 
  } 
  print $fh "  </tr>\n"; 
 } 
print $fh "</table>\n";

$sth->finish (); 
$dbh->disconnect ();
close($fh);
}
exit (0);


